package tt.dz.service.imp.middleware;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.stereotype.Service;

import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import tt.dz.dao.base.BaseDao;
import tt.dz.dao.ext.station.OmsStationDao;
import tt.dz.entity.base.OmsLog;
import tt.dz.entity.charger.OmsCharger;
import tt.dz.entity.station.OmsStation;
import tt.dz.entity.station.OmsStationScore;
import tt.dz.service.middleware.OmsCharStationService;
import tt.dz.util.StringUtil;

@Service
@Transactional(propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
public class OmsCharStationServiceImpl implements OmsCharStationService
{

	private Log log = LogFactory.getLog(OmsCharStationServiceImpl.class);

	@Autowired
	BaseDao baseDao;
	//	@Autowired
	//	OmsStationDao omsStationDao;

	@Override
	public String getStationByChargerId(String chargerId)
	{
		OmsCharger charger = new OmsCharger();
		charger.setCharger_id(chargerId);
		OmsCharger chargerReturn = (OmsCharger) baseDao.findOneExample(charger, null, null);
		return chargerReturn.getStation_id();
	}

	@Override
	public List<OmsStation> getStationsByCoord(String x, String y, String distance)
	{
		String sql = "select s.coordinate_x, s.coordinate_y, t.station_id from oms_station_location s inner join oms_station t on s.station_id = t.station_id where s.coordtype = 1 and s.valid_flag = 1 and t.valid_flag = 1";

		float x1, y1, di;

		StringBuffer sb = new StringBuffer();

		try
		{
			x1 = Float.parseFloat(x);
			y1 = Float.parseFloat(y);
			di = Float.parseFloat(distance);
		}
		catch (NumberFormatException e)
		{
			log.error(e.getMessage(), e);
			throw e;
		}

		List<Map<String, Object>> l = baseDao.findDataBySql(sql);
		int i = 0;
		for (Map<String, Object> row : l)
		{
			log.debug(row);
			String coordinate_x = (String) row.get("coordinate_x");
			String coordinate_y = (String) row.get("coordinate_y");
			if (coordinate_x != null && coordinate_y != null)
			{
				try
				{
					float x0 = Float.parseFloat(coordinate_x);
					float y0 = Float.parseFloat(coordinate_y);

					double t = d * Math.sqrt((x0 - x1) * (x0 - x1) + (y0 - y1) * (y0 - y1));
					log.debug(t);
					if (t < di)
					{
						if (i > 0)
							sb.append(", ");
						sb.append("'").append(row.get("station_id")).append("'");
						i++;
					}

				}
				catch (NumberFormatException e)
				{
					log.error(e.getMessage(), e);
				}
			}
		}

		List<OmsStation> list = new ArrayList<>();

		if (sb.length() > 0)
		{
			String s = "SELECT * FROM oms_station WHERE station_id IN (" + sb.toString() + ")";
			list = baseDao.getJdbcTemplate().query(s, new PreparedStatementSetter()
			{
				@Override
				public void setValues(PreparedStatement arg0) throws SQLException
				{

				}
			}, new BeanPropertyRowMapper<>(OmsStation.class));
		}

		return list;
	}

	private static final double d = 111317.59257475018;

	@Override
	public List<Map<String, Object>> getStationByLocation(String x, String y, String number) {
		StringBuffer sql = new StringBuffer();
		sql.append("select a.*,b.coordinate_x,b.coordinate_y,");
		sql.append("ROUND(6371.004*2*ASIN(SQRT(POW(SIN((").append(y).append("*PI()/180-coordinate_y*PI()/180)/2),2)+COS(").append(y).append("*PI()/180)*COS(coordinate_y*PI()/180)*POW(SIN((").append(x).append("*PI()/180-coordinate_x*PI()/180)/2),2)))*1000) AS dis_m ");
		sql.append("from oms_station a,oms_station_location b where a.station_id=b.station_id  and a.valid_flag=1 and b.valid_flag=1 order by dis_m asc limit ").append(number);

		List<Map<String, Object>> listTmp = baseDao.findDataBySql(sql.toString());
		return listTmp;
	}

	@Override
	public List<Map<String, Object>> getStationByName(String stationName, String cityCode)
	{
		StringBuffer sql = new StringBuffer();
		sql.append("select a.*,b.coordinate_x,b.coordinate_y from oms_station a,oms_station_location b ");
		sql.append("where a.station_id=b.station_id and a.valid_flag=1 and b.valid_flag=1 and ( a.station_name like '%").append(stationName).append("%'");
		sql.append(" or a.address like '%").append(stationName).append("%' )");
		if (StringUtil.isNotBlank(cityCode))
		{
			sql.append(" and concat(substring(a.district_code,1,4 ),'00')='").append(cityCode).append("'");
		}
		List<Map<String, Object>> listTmp = baseDao.findDataBySql(sql.toString());
		return listTmp;
	}

	@Override
	public void addStationScore(OmsStationScore omsStationScore) {
		baseDao.save(omsStationScore);	
	}

	@Override
	public boolean updateStationScore() {
		String sql = "";
		sql = "update oms_station a set a.score=(select avg(b.score) from oms_station_score b where a.station_id = b.station_id )"; 

		return baseDao.exec(sql);
		
	}

}
